/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package me.zhengjie.modules.file.repository;

import me.zhengjie.modules.file.domain.File;
import me.zhengjie.modules.file.service.dto.FileOwnModel;
import me.zhengjie.modules.file.service.dto.FilePublicModel;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

/**
* @website https://el-admin.vip
* @author FireWork
* @date 2021-04-02
**/
public interface FileRepository extends JpaRepository<File, Integer>, JpaSpecificationExecutor<File> {


    /**
     * 查询所有公开文件
     * */
    @Query(value ="select new me.zhengjie.modules.file.service.dto.FilePublicModel(f.id,u.username," +
            "l.name,l.path,l.type,l.size,l.realName,l.createTime) from File f right join" +
            " LocalStorage l on l.id=f.forStorageId " +
            " left join User u on u.id=f.audienceId  where f.isPublic= 1")
    Page<FilePublicModel> findPublicFile(Pageable pageable);
    /**
     * 登录用户可查看的文件
     * */
    @Query(value = "select new me.zhengjie.modules.file.service.dto.FileOwnModel(f.id,f.audienceId," +
            "l.createTime,f.isPublic,l.name,l.path,l.type,l.size) from File f right join" +
            " LocalStorage l on l.id=f.forStorageId where f.isPublic=1 or( f.isPublic= 0 and f.audienceId= :loginId )")
    Page<FileOwnModel> findOwnFile(@Param(value = "loginId") Integer loginId, Pageable pageable);
    /**
     * 管理员查看所有上传文件
     */
    @Query(value = "select l.storage_id,l.real_name," +
            " f.public_bit,l.create_time,GROUP_CONCAT(u.username) as usernames from sys_file f " +
            " LEFT JOIN sys_user u on f.user_id=u.user_id" +
            " RIGHT JOIN tool_local_storage l ON l.storage_id=f.file_id group by storage_id",
            countQuery = "SELECT count(*) FROM sys_file",
            nativeQuery = true)
    Page<Object[]> findAdminFile(Pageable pageable);

    /**
     * 查询是否有下载文件的权限
     * @param loginId
     * @param fId
     * @return
     */
    @Query(value = "select l.id,l.realName,l.path from File f right JOIN LocalStorage l on f.forStorageId=l.id " +
            " where (f.audienceId= :loginId or f.isPublic=1 ) and f.id= :fId")
    Object verifyFileDownload(@Param(value = "loginId") Integer loginId,@Param(value = "fId") Integer fId);
}
